insert overwrite table jms_dm.dm_whole_effect_abnormal_detail_dt partition(dt)
select
      wide.waybill_no
     ,wide.sign_date                --签收日期
     ,wide.send_network_code        --始发网点编码
     ,wide.send_network_name        --始发网点名称
     ,wide.send_fran_code           --始发加盟商code
     ,wide.send_fran_name           --始发加盟商名称
     ,wide.send_agent_code          --始发代理区code
     ,wide.send_agent_name          --始发代理区名称
     ,wide.send_regional_id         --始发大区id
     ,wide.send_regional_desc       --始发大区名称
     ,wide.send_area_id             --始发区县id
     ,wide.send_area_desc           --始发区县名称
     ,wide.send_city_id             --始发城市id
     ,wide.send_city_desc           --始发城市名称
     ,wide.send_provider_id         --始发省份id
     ,wide.send_provider_desc       --始发省份名称
     ,wide.sign_network_code        --签收网点编码
     ,wide.sign_network_name        --签收网点名称
     ,wide.sign_fran_code           --签收加盟商code
     ,wide.sign_fran_name           --签收加盟商名称
     ,wide.sign_agent_code          --签收代理区code
     ,wide.sign_agent_name          --签收代理区名称
     ,wide.sign_regional_id         --签收大区id
     ,wide.sign_regional_desc       --签收大区名称
     ,wide.sign_area_id             --签收区县id
     ,wide.sign_area_desc           --签收区县名称
     ,wide.sign_city_id             --签收城市id
     ,wide.sign_city_desc           --签收城市名称
     ,wide.sign_provider_id         --签收省份id
     ,wide.sign_provider_desc       --签收省份名称
     ,wide.ordersource_code         --订单来源编码
     ,wide.ordersource_name         --订单来源名称                                                
     ,wide.deliver_user_code           as deliver_user_code        --出仓员编码              
     ,wide.deliver_user_name           as deliver_user_name        --出仓员名称              
     ,wide.end_site_arrival_later_time as last_arrival_scan_time   --末端网点到件时间
     ,wide.deliver_scan_time           as deliver_scan_time        --出仓时间
     ,wide.instore_scan_time           as instore_scan_time        --入库时间
     ,wide.outstore_scan_time          as outstore_scan_time       --出库时间
     ,wide.aging_sign_scan_time        as aging_sign_scan_time     --时效签收时间
     ,wide.actual_sign_scan_time       as actual_sign_scan_time    --实际签收时间
     ,round(wide.end_network_arrival_deliver_difftime ,2) as end_network_arrival_deliver_time --末端网点到件-派件时长
     ,round(wide.deliver_aging_sign_difftime          ,2) as deliver_aging_sign_time          --派件-时效签收时长
     ,round(wide.real_pre_sign_difftime               ,2) as actual_aging_sign_time           --时效签收-实际签收时长
     ,case when wide.deliver_aging_sign_difftime > 24 then 1 else 0 end as if_deliver_24h     --派件-时效签收超24H标签
     ,case when diff.waybill_no is not null then 1 else 0 end as if_difficult                 --是否问题件,1:是,0:否_60天
     ,difficult_type_code              as difficult_type_code --近60天问题件类型编码             
     ,difficult_type_desc              as difficult_type_desc --近60天问题件类型描述
     ,wide.sign_date as dt --揽收日期=分区
from jms_dws.dws_wide_unsign_whole_effect_dt wide
left join (
    select *
    from(
        select
             waybill_no
            ,second_level_type_code as difficult_type_code
            ,second_level_type_name as difficult_type_desc
            ,row_number() over(partition by waybill_no order by scan_time desc) as rn   
        from jms_dwd.dwd_sqs_registration_problem_piece_base_dt
        where dt between date_sub('{{ execution_date | cst_ds }}',30) and '{{ execution_date | cst_ds }}'
          and second_level_type_name not in (
           '不可抗力因素'
          ,'政府查件'
          ,'异常天气'
          ,'自然灾害'
          ,'疫情管制'
          ,'疫情滞留'
          ,'客户地址无法进入'
          )
    ) a where a.rn = 1
) diff on wide.waybill_no = diff.waybill_no
where wide.dt between date_sub('{{ execution_date | cst_ds }}',30) and '{{ execution_date | cst_ds }}'
  and wide.send_network_code is not null
  and wide.sign_date between date_sub('{{ execution_date | cst_ds }}',10) and '{{ execution_date | cst_ds }}'
  and wide.if_reback    <> 1
  and wide.if_end_piece <> 1
  and wide.if_intercept <> 1
  and diff.waybill_no is not null --是否问题件
distribute by wide.sign_date,pmod(hash(rand()),30)
;
